﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace RegisterationCommon.Common
{
    public sealed class DBAction
    {
        #region 获取数据列表
        /// <summary>
        /// 根据SQL语句获取DataTable
        /// </summary>
        /// <param name="StrSQL">SQL语句</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static DataTable GetDTFromSQL(string StrSQL)
        {
            return DBHelper.ExecuteDataset(DBHelper.ConnString, CommandType.Text, StrSQL).Tables[0];
        }

        /// <summary>
        /// 根据SQL语句获取DataSet
        /// </summary>
        /// <param name="StrSQL">SQL语句</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static DataSet GetDSFromSQL(string StrSQL)
        {
            return DBHelper.ExecuteDataset(DBHelper.ConnString, CommandType.Text, StrSQL);
        }

        /// <summary>
        /// 根据SQL语句与参数获取DataTable
        /// </summary>
        /// <param name="StrSQL">SQL语句</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static DataTable GetDTFromSQL(string StrSQL, RParams VParams)
        {
            return DBHelper.ExecuteDataset(DBHelper.ConnString, CommandType.Text, StrSQL, VParams.Items.ToArray()).Tables[0];
        }

        /// <summary>
        /// 根据SQL语句与参数获取DataSet
        /// </summary>
        /// <param name="StrSQL">SQL语句</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static DataSet GetDSFromSQL(string StrSQL, RParams VParams)
        {
            return DBHelper.ExecuteDataset(DBHelper.ConnString, CommandType.Text, StrSQL, VParams.Items.ToArray());
        }

        /// <summary>
        /// 根据表、列、条件与参数获取DataTable
        /// </summary>
        /// <param name="TBName">ETableName表名</param>
        /// <param name="Columns">列字段</param>
        /// <param name="WhereFields">条件</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static DataTable GetDTFromSQL(ETableName TBName, string Columns, string WhereFields, RParams VParams)
        {
            return GetDTFromSQL(TBName.ToString(), Columns, WhereFields, VParams);
        }
        /// <summary>
        /// 根据表、列、条件与参数获取DataSet
        /// </summary>
        /// <param name="TBName">ETableName表名</param>
        /// <param name="Columns">列字段</param>
        /// <param name="WhereFields">条件</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static DataSet GetDSFromSQL(ETableName TBName, string Columns, string WhereFields, RParams VParams)
        {
            return GetDSFromSQL(TBName.ToString(), Columns, WhereFields, VParams);
        }

        /// <summary>
        /// 根据表、列、条件与参数获取DataTable
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="Columns">列字段</param>
        /// <param name="WhereFields">条件</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static DataTable GetDTFromSQL(string TBName, string Columns, string WhereFields, RParams VParams)
        {
            string strSQL = "Select " + Columns + " From " + TBName + " Where " + WhereFields;
            return GetDTFromSQL(strSQL, VParams);
        }
        /// <summary>
        /// 根据表、列、条件与参数获取DataSet
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="Columns">列字段</param>
        /// <param name="WhereFields">条件</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static DataSet GetDSFromSQL(string TBName, string Columns, string WhereFields, RParams VParams)
        {
            string strSQL = "Select " + Columns + " From " + TBName + " Where " + WhereFields;
            return GetDSFromSQL(strSQL, VParams);
        }

        /// <summary>
        /// 根据ETableName中定义的表名获取DataTable
        /// </summary>
        /// <param name="TBName">ETableName类型的表名</param>
        /// <returns></returns>
        public static DataTable GetDTFromTBName(ETableName TBName)
        {
            return GetDTFromTBName(TBName.ToString());
        }

        /// <summary>
        /// 根据ETableName中定义的表名获取DataSet
        /// </summary>
        /// <param name="TBName">ETableName类型的表名</param>
        /// <returns></returns>
        public static DataSet GetDSFromTBName(ETableName TBName)
        {
            return GetDSFromTBName(TBName.ToString());
        }

        /// <summary>
        /// 根据表名字符串获取DataTable
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <returns></returns>
        public static DataTable GetDTFromTBName(string TBName)
        {
            string strSQL = "Select * From " + TBName;
            return DBHelper.ExecuteDataset(DBHelper.ConnString, CommandType.Text, strSQL).Tables[0];
        }

        /// <summary>
        /// 根据表名字符串获取DataTable
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <returns></returns>
        public static DataSet GetDSFromTBName(string TBName)
        {
            TBName = "Select * From " + TBName;
            return DBHelper.ExecuteDataset(DBHelper.ConnString, CommandType.Text, TBName);
        }

        #endregion

        #region 通用分页数据获取
        /// <summary>
        /// 通用分页查询（单表）
        /// </summary>
        /// <param name="rParams">参数列表</param>
        /// <returns></returns>
        public static DataTable GetPageList(RParams rParams)
        {
            return GetPageListDS(rParams).Tables[0];
        }

        /// <summary>
        /// 通用分页查询（单表）
        /// </summary>
        /// <param name="rParams">参数列表</param>
        /// <returns></returns>
        public static DataSet GetPageListDS(RParams rParams)
        {
            return DBAction.ExecSP(EStoredProc.sp_select_page, rParams);
        }
        #endregion

        #region 添加数据
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="TBName">ETableName中定义的表名</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static bool Insert(ETableName TBName, RParams VParams)
        {
            return Insert(TBName.ToString(), VParams);
        }
        public static int Insertselect(ETableName TBName, RParams VParams)
        {
            return Insertselect(TBName.ToString(), VParams);
        }
        public static int Insertselect(string TBName, RParams VParams)
        {
            string[] FieldsAndParams = FieldsAndParamsName(VParams);
            string strSQL = "";

            strSQL = "Insert Into " + TBName + "(" + FieldsAndParams[0] + ") Values(" + FieldsAndParams[1] + ") select @@identity";
            int i = Convert.ToInt32(DBHelper.ExecuteScalar(DBHelper.ConnString, CommandType.Text, strSQL, VParams.Items.ToArray()));
            if (i> 0)
            {
                return i;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="Fields">列名字符串</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static bool Insert(string TBName, RParams VParams)
        {
            string[] FieldsAndParams = FieldsAndParamsName(VParams);
            string strSQL = "";

            strSQL = "Insert Into " + TBName + "(" + FieldsAndParams[0] + ") Values(" + FieldsAndParams[1] + ")";

            if (DBHelper.ExecuteNonQuery(DBHelper.ConnString, CommandType.Text, strSQL, VParams.Items.ToArray()) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 添加数据并返回ID
        /// <summary>
        /// 添加数据返回数据ID
        /// </summary>
        /// <param name="TBName">ETableName中定义的表名</param>
        /// <param name="Fields">列名字符串</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static int InsertReturnID(ETableName TBName, RParams VParams)
        {
            return InsertReturnID(TBName.ToString(), VParams);
        }

        /// <summary>
        /// 添加数据返回数据ID
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="Fields">列名字符串</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns></returns>
        public static int InsertReturnID(string TBName, RParams VParams)
        {
            string[] FieldsAndParams = FieldsAndParamsName(VParams);
            string strSQL = "";
            strSQL = "Insert Into " + TBName + "(" + FieldsAndParams[0] + ") Values(" + FieldsAndParams[1] + ");SELECT @@identity";
            int ID = Convert.ToInt32(DBHelper.ExecuteScalar(DBHelper.ConnString, CommandType.Text, strSQL, VParams.Items.ToArray()));

            return ID;
        }
        #endregion

        #region 修改数据
        /// <summary>
        /// 更新修改数据
        /// </summary>
        /// <param name="TBName">ETableName中定义的表名</param>
        /// <param name="Fields">列名字符串</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <param name="WhereFields">条件</param>
        /// <returns></returns>
        public static bool Update(ETableName TBName, string Fields, string WhereFields, RParams VParams)
        {
            return Update(TBName.ToString(), Fields, WhereFields, VParams);
        }

        /// <summary>
        /// 更新修改数据
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="Fields">列名字符串</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <param name="WhereFields">条件</param>
        /// <returns></returns>
        public static bool Update(string TBName, string Fields, string WhereFields, RParams VParams)
        {
            string valParamRelation = FieldsToRelation(Fields);
            string strSQL = "Update " + TBName + " Set " + valParamRelation + " Where " + WhereFields;

            if (DBHelper.ExecuteNonQuery(DBHelper.ConnString, CommandType.Text, strSQL, VParams.Items.ToArray()) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 删除数据
        /// <summary>
        /// 删除数据，自定义条件语句
        /// </summary>
        /// <param name="TBName">ETableName中定义的表名</param>
        /// <param name="WhereFields">条件语句</param>
        /// <returns></returns>
        public static bool Delete(ETableName TBName, string WhereFields)
        {
            return Delete(TBName.ToString(), WhereFields);
        }

        /// <summary>
        /// 删除数据，自定义条件语句
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="WhereField">条件语句</param>
        /// <returns></returns>
        public static bool Delete(string TBName, string WhereFields)
        {
            string strSQL = "Delete " + TBName + " Where " + WhereFields;

            if (DBHelper.ExecuteNonQuery(DBHelper.ConnString, CommandType.Text, strSQL) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除数据，自定义参数条件与参数集合
        /// </summary>
        /// <param name="TBName">ETableName中定义的表名</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <param name="WhereFields">参数条件</param>
        /// <returns></returns>
        public static bool Delete(ETableName TBName, string WhereFields, RParams VParams)
        {
            return Delete(TBName.ToString(), WhereFields, VParams);
        }

        /// <summary>
        /// 删除数据，自定义参数条件与参数集合
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <param name="WhereFields">参数条件</param>
        /// <returns></returns>
        public static bool Delete(string TBName, string WhereFields, RParams VParams)
        {
            string strSQL = "Delete " + TBName + " Where " + WhereFields;

            if (DBHelper.ExecuteNonQuery(DBHelper.ConnString, CommandType.Text, strSQL, VParams.Items.ToArray()) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 获取记录总数
        public static int GetRecordCount(ETableName TBName, string WhereFields, RParams VParams)
        {
            return GetRecordCount(TBName.ToString(), WhereFields, VParams);
        }
        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="TBList">表名列表，如：Table1 t1,Table2,t2</param>
        /// <param name="strWhere">Where查询条件</param>
        /// <returns>返回记录数</returns>
        public static int GetRecordCount(string TBList, string WhereFields, RParams VParams)
        {
            string strSQL = "select count(*) from " + TBList;
            if (WhereFields != "")
            {
                strSQL += " where " + WhereFields;
            }

            return Convert.ToInt32(DBHelper.ExecuteScalar(DBHelper.ConnString, CommandType.Text, strSQL, VParams.Items.ToArray()));
        }
        #endregion

        #region 判断数据是否存在
        /// <summary>
        /// 判断是否有重复数据(存在false,不存在true)
        /// </summary>
        /// <param name="TBList">表名列表，如：Table1 t1,Table2,t2</param>
        /// <param name="strWhere">Where查询条件</param>
        /// <returns>返回数据是否存在</returns>
        public static bool HasData(string TBList, string WhereFields, RParams VParams)
        {
            if (GetRecordCount(TBList, WhereFields, VParams) == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region 公共方法
        /// <summary>
        /// 根据列名创建参数名称列表，常用于添加数据
        /// </summary>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns>返回创建后的参数名称列表（字段:a,b,c,...  值@a,@b,@c,...）</returns>
        private static string[] FieldsAndParamsName(RParams VParams)
        {
            string FieldList = "";
            string VParamList = "";

            for (int n = 0; n < VParams.Items.Count; n++)
            {
                FieldList += VParams.Items[n].ParameterName + ",";
                VParamList += "@" + VParams.Items[n].ParameterName + ",";
            }

            FieldList = FieldList.Substring(0, FieldList.Length - 1);
            VParamList = VParamList.Substring(0, VParamList.Length - 1);

            return new string[] { FieldList, VParamList };
        }

        /// <summary>
        /// 根据列名创建修改字段与值参数的关系，常用于修改数据
        /// </summary>
        /// <param name="Fields">绑定字段字符串</param>
        /// <returns>返回创建后的修改字段与值参数的关系（a=@a,b=@b,...）</returns>
        private static string FieldsToRelation(string Fields)
        {
            if (Fields == "")
            {
                return Fields;
            }

            string[] FieldsArr = Fields.Split(',');
            Fields = "";
            for (int n = 0; n < FieldsArr.Length; n++)
            {
                Fields += FieldsArr[n] + "=@" + FieldsArr[n] + ",";
            }
            Fields = Fields.Substring(0, Fields.Length - 1);
            return Fields;
        }
        #endregion

        #region 修改排序
        /// <summary>
        /// 根据表名与索引字段对数据进行排序对换
        /// </summary>
        /// <param name="TBName">数据表</param>
        /// <param name="IDName">索引列名</param>
        /// <param name="FromIDValue">转换排序的数据索引值</param>
        /// <param name="ToIDValue">被转换排序的数据索引值</param>
        public static void MdfSort(ETableName TBName, string IDName, long FromIDValue, long ToIDValue)
        {
            MdfSort(TBName.ToString(), IDName, FromIDValue, ToIDValue);
        }

        /// <summary>
        /// 根据表名与索引字段对数据进行排序对换
        /// </summary>
        /// <param name="TBName">数据表</param>
        /// <param name="IDName">索引列名</param>
        /// <param name="FromIDValue">转换排序的数据索引值</param>
        /// <param name="ToIDValue">被转换排序的数据索引值</param>
        public static void MdfSort(string TBName, string IDName, long FromIDValue, long ToIDValue)
        {
            RParams GParams = new RParams();

            //获取来源的排序值
            GParams.Add(IDName, FromIDValue, SqlDbType.BigInt);
            long FromSort = Convert.ToInt64(DBAction.GetDTFromSQL(TBName, "Sort", IDName + "=@" + IDName, GParams).Rows[0][0]);

            //获取目的排序值
            GParams.Items[0].Value = ToIDValue;
            long ToSort = Convert.ToInt64(DBAction.GetDTFromSQL(TBName, "Sort", IDName + "=@" + IDName, GParams).Rows[0][0]);

            //调换两行数据的排序
            SetSort(TBName, IDName, FromIDValue, ToSort);
            SetSort(TBName, IDName, ToIDValue, FromSort);
        }

        /// <summary>
        /// 排序修改
        /// (将原有的排序值附给新排序所在数据的排序字段值)
        /// </summary>
        /// <param name="FromID">需要转换排序的数据ID</param>
        /// <param name="ToID">被转换排序的数据ID</param>
        /// <param name="TBName">数据表</param>
        /// <returns></returns>
        public static void MdfSort(ETableName TBName, long FromID, long ToID)
        {
            MdfSort(TBName.ToString(), FromID, ToID);
        }

        /// <summary>
        /// 排序修改
        /// (将原有的排序值附给新排序所在数据的排序字段值)
        /// </summary>
        /// <param name="TBName">需要转换排序的数据ID</param>
        /// <param name="FromID">被转换排序的数据ID</param>
        /// <param name="ToID">目的行排序</param>
        /// <returns></returns>
        public static void MdfSort(string TBName, long FromID, long ToID)
        {
            RParams GParams = new RParams();

            //获取来源的排序值
            GParams.Add("ID", FromID, SqlDbType.BigInt);
            long FromSort = Convert.ToInt64(DBAction.GetDTFromSQL(TBName, "Sort", "ID=@ID", GParams).Rows[0][0]);

            //获取目的排序值
            GParams.Items[0].Value = ToID;
            long ToSort = Convert.ToInt64(DBAction.GetDTFromSQL(TBName, "Sort", "ID=@ID", GParams).Rows[0][0]);

            //调换两行数据的排序
            SetSort(TBName, "", FromID, ToSort);
            SetSort(TBName, "", ToID, FromSort);
        }
        #endregion

        #region 设置排序值
        /// <summary>
        /// 设置数据排序值
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="ID">数据ID</param>
        /// <param name="SortValue">排序值</param>
        public static void SetSort(ETableName TBName, long IDValue, long SortValue)
        {
            SetSort(TBName.ToString(), "ID", IDValue, SortValue);
        }

        /// <summary>
        /// 设置数据排序值
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="ID">数据ID</param>
        /// <param name="SortValue">排序值</param>
        public static void SetSort(string TBName, long IDValue, long SortValue)
        {
            SetSort(TBName, "ID", IDValue, SortValue);
        }

        /// <summary>
        /// 设置数据排序值
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="ID">数据ID</param>
        /// <param name="SortValue">排序值</param>
        public static void SetSort(ETableName TBName, string IDName, long IDValue, long SortValue)
        {
            if (IDName == "") { IDName = "ID"; }

            SetSort(TBName.ToString(), IDName, IDValue, SortValue);
        }

        /// <summary>
        /// 设置数据排序值
        /// </summary>
        /// <param name="TBName">表名</param>
        /// <param name="ID">数据ID</param>
        /// <param name="SortValue">排序值</param>
        public static void SetSort(string TBName, string IDName, long IDValue, long SortValue)
        {
            if (IDName == "") { IDName = "ID"; }

            RParams sRP = new RParams();
            sRP.Add(IDName, IDValue, SqlDbType.BigInt);
            sRP.Add("Sort", SortValue, SqlDbType.BigInt);
            DBAction.Update(TBName, "Sort", IDName + "=@" + IDName, sRP);
        }
        #endregion

        #region 执行存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="SPName">存储过程名称</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns>返回结果集DataSet</returns>
        public static DataSet ExecSP(EStoredProc SPName, RParams VParams)
        {
            return ExecSP(SPName.ToString(), VParams);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="SPName">存储过程名称</param>
        /// <param name="VParams">RParams类型的参数集合</param>
        /// <returns>返回结果集DataSet</returns>
        public static DataSet ExecSP(string SPName, RParams VParams)
        {
            return DBHelper.ExecuteDataset(DBHelper.ConnString, CommandType.StoredProcedure, SPName, VParams.Items.ToArray());
        }
        /// <summary>
        /// 执行无参数存储过程
        /// </summary>
        /// <param name="SPName">存储过程名称</param>
        /// <returns>返回结果集DataSet</returns>
        public static DataSet ExecSP(string SPName)
        {
            return DBHelper.ExecuteDataset(DBHelper.ConnString, CommandType.StoredProcedure, SPName);
        }
        #endregion

        #region DataTable转换Json格式
        /// <summary>
        /// （数据列表）将DataTable中的数据转换成JSON格式
        /// </summary>
        /// <param name="dt">数据源DataTable</param>
        /// <param name="displayCount">是否输出数据总条数</param>
        /// <param name="totalcount">JSON中显示的数据总条数</param>
        /// <returns>返回格式{rows:[{"":""},{"":""}],"total":"100"}</returns>
        public static string DataTableToJson(DataTable dt, bool displayCount, int totalCount)
        {
            StringBuilder JsonString = new StringBuilder();

            if (dt != null)
            {
                JsonString.Append("{ ");
                JsonString.Append("\"rows\":[ ");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    JsonString.Append("{ ");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (j < dt.Columns.Count - 1)
                        {
                            //if (dt.Rows[i][j] == DBNull.Value) continue;
                            if (dt.Columns[j].DataType == typeof(bool))
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" +
                                                  dt.Rows[i][j].ToString().ToLower() + ",");
                            }
                            else if (dt.Columns[j].DataType == typeof(string))
                            {
                                //JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" +
                                //                  dt.Rows[i][j].ToString().Replace("\"", "\\\"") + "\",");
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" +
                                    dt.Rows[i][j].ToString().Replace("\"", "\\\"").Replace("\r", " ").Replace("\t"," ") + "\",");
                            }
                            else
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j] + "\",");
                            }
                        }
                        else if (j == dt.Columns.Count - 1)
                        {
                            //if (dt.Rows[i][j] == DBNull.Value) continue;
                            if (dt.Columns[j].DataType == typeof(bool))
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" +
                                                  dt.Rows[i][j].ToString().ToLower());
                            }
                            else if (dt.Columns[j].DataType == typeof(string))
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" +
                                                  dt.Rows[i][j].ToString().Replace("\"", "\\\"") + "\"");
                            }
                            else
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j] + "\"");
                            }
                        }
                    }
                    if (i == dt.Rows.Count - 1)
                    {
                        JsonString.Append("} ");
                    }
                    else
                    {
                        JsonString.Append("}, ");
                    }
                }
                JsonString.Append("]");

                if (displayCount)
                {
                    JsonString.Append(",");

                    JsonString.Append("\"total\":");
                    JsonString.Append(totalCount);
                }

                JsonString.Append("}");
                return JsonString.ToString().Replace("\n", "");
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// （下拉列表框）DataTable转换Json
        /// </summary>
        /// <param name="dt">数据源DataTable</param>
        /// <returns>返回格式[{'':''},{"",""}]</returns>
        public static string DataTableToJson(DataTable dt)
        {
            StringBuilder JsonString = new StringBuilder();

            if (dt != null)
            {
                //JsonString.Append("{ ");
                //JsonString.Append("\"rows\":[ ");
                JsonString.Append("[ ");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    JsonString.Append("{ ");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (j < dt.Columns.Count - 1)
                        {
                            //if (dt.Rows[i][j] == DBNull.Value) continue;
                            if (dt.Columns[j].DataType == typeof(bool))
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" +
                                                  dt.Rows[i][j].ToString().ToLower() + ",");
                            }
                            else if (dt.Columns[j].DataType == typeof(string))
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" +
                                                  dt.Rows[i][j].ToString().Replace("\"", "\\\"") + "\",");
                            }
                            else
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j] + "\",");
                            }
                        }
                        else if (j == dt.Columns.Count - 1)
                        {
                            //if (dt.Rows[i][j] == DBNull.Value) continue;
                            if (dt.Columns[j].DataType == typeof(bool))
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" +
                                                  dt.Rows[i][j].ToString().ToLower());
                            }
                            else if (dt.Columns[j].DataType == typeof(string))
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" +
                                                  dt.Rows[i][j].ToString().Replace("\"", "\\\"") + "\"");
                            }
                            else
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j] + "\"");
                            }
                        }
                    }
                    if (i == dt.Rows.Count - 1)
                    {
                        JsonString.Append("} ");
                    }
                    else
                    {
                        JsonString.Append("}, ");
                    }
                }
                JsonString.Append("]");

                return JsonString.ToString().Replace("\n", "");
            }
            else
            {
                return null;
            }
        }

        #endregion

        #region 数据库备份还原
        #region 备份数据库
        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="dbName">要备份的数据库名称,不指定则备份当前数据库</param>
        /// <param name="path">备份文件的存放目录,不指定则使用SQL默认的备份目录</param>
        /// <param name="fName">备份文件名,文件名中可以用"DBNAME"代表数据库名,"DATE"代表日期,"TIME"代表时间</param>
        /// <param name="type">备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份</param>
        /// <param name="isAppendFile">追加或覆盖备份文件</param>
        /// <returns>无</returns>
        public static bool BakupDB(string dbName, string path, string fName, string type, bool isAppendFile)
        {
            //定义备份数据库的存储过程名称
            string bakStoreCmd = "sp_backupdb";
            SqlParameter[] bParameter = { 
                                            new SqlParameter("@DBName",SqlDbType.NVarChar),
                                            new SqlParameter("@BakPath",SqlDbType.NVarChar,260),
                                            new SqlParameter("@BakFName",SqlDbType.NVarChar,260),
                                            new SqlParameter("@BakType",SqlDbType.NVarChar,10),
                                            new SqlParameter("@IsAppendFile",SqlDbType.Bit)
                                        };
            bParameter[0].Value = dbName;
            bParameter[1].Value = path;
            bParameter[2].Value = fName;
            bParameter[3].Value = type;
            bParameter[4].Value = isAppendFile;

            try
            {
                DBHelper.ExecuteNonQuery(DBHelper.ConnString, CommandType.StoredProcedure, bakStoreCmd, bParameter);
                Config.Current.LastDBBakupTime = DateTime.Now;
                Config.Current.SaveSystemConfig();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        #endregion

        #region 恢复数据库
        /// <summary>
        /// 恢复数据库
        /// </summary>
        /// <param name="DBName">定义恢复后的数据库名,默认为备份的文件名</param>
        /// <param name="BakFile">定义要恢复的备份文件名</param>
        /// <returns>返回bool值</returns>
        public static bool RestoreDB(string dbName, string fromeFile)
        {
            SqlConnection restoreConn = new SqlConnection(DBHelper.MasterConnString);
            restoreConn.Open();

            //强制停止相关链接进程
            SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='" + dbName + "'", restoreConn);
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            ArrayList list = new ArrayList();
            while (dr.Read())
            {
                list.Add(dr.GetInt16(0));
            }
            dr.Close();
            for (int i = 0; i < list.Count; i++)
            {
                //cmd = new SqlCommand("KILL {0}" + list[i], ReConn);
                cmd = new SqlCommand(string.Format("KILL {0}", list[i]), restoreConn);
                cmd.ExecuteNonQuery();
            }

            SqlCommand cmdRT = new SqlCommand();
            cmdRT.CommandType = CommandType.Text;
            cmdRT.Connection = restoreConn;
            cmdRT.CommandText = @"restore database " + dbName + " from disk='" + fromeFile + "' WITH REPLACE";
            try
            {
                cmdRT.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                restoreConn.Close();
            }
        }
        #endregion
        #endregion
    }
}
